﻿using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.IO;
using System.Linq;
using System.Threading;

using Blog.Framework;
using Blog.Framework.DataAccess;
using Blog.Framework.Models;
using Blog.Framework.Utility;

namespace Blog.Providers.LinqToSqlBusinessObjectProvider
{
    public class LinqBusinessObjectProvider : BusinessObjectProvider, IDisposable
    {
        BlogDatabaseDataContext mDB;

        public LinqBusinessObjectProvider()
        {
            mDB = new BlogDatabaseDataContext(ConnectionString);
        }
        
        public override PagedList<PostSummary> GetRecentPostSummary(int index, int postsPerPage)
        {
                var posts = from p in DB.blog_post_tbls
                            where p.ispublished == true && p.isdeleted == false
                            orderby p.create_dt descending
                            select new PostSummary()
                            {
                                Id = p.post_id,
                                Title = p.title,
                                Url = p.url,
                                Body = p.body,
                                Created = p.create_dt,
                                Updated = p.update_dt,
                                Tags = (from pt in DB.blog_post_tag_lnks join t in DB.blog_tag_tbls on pt.tag_id equals t.tag_id where pt.post_id == p.post_id select new Tag() { Id = t.tag_id, Name = t.tag_nm, Url = t.url }).ToList()
                            };
                return new PagedList<PostSummary>(posts, index, postsPerPage);
        }

        public override PagedList<PostSummary> GetPostsForTag(string tag, int index, int postsPerPage)
        {
                var posts = from p in DB.blog_post_tbls
                            join pt in DB.blog_post_tag_lnks on p.post_id equals pt.post_id
                            join t in DB.blog_tag_tbls on pt.tag_id equals t.tag_id
                            where p.ispublished == true && p.isdeleted == false && t.tag_nm == tag
                            orderby p.create_dt descending
                            select new PostSummary()
                            {
                                Id = p.post_id,
                                Title = p.title,
                                Url = p.url,
                                Body = p.body,
                                Created = p.create_dt,
                                Updated = p.update_dt,
                                Tags = (from pt2 in DB.blog_post_tag_lnks join t2 in DB.blog_tag_tbls on pt2.tag_id equals t2.tag_id where pt2.post_id == p.post_id select new Tag() { Id = t2.tag_id, Name = t2.tag_nm, Url = t2.url }).ToList()
                            };
                return new PagedList<PostSummary>(posts, index, postsPerPage);
        }

        public override PagedList<PostSummary> GetPostsForMonth(int month, int year, int index, int postsPerPage)
        {
            DateTime start = new DateTime(year, month, 1);
            DateTime end = start.AddMonths(1);

            var posts = from p in DB.blog_post_tbls
                        join pt in DB.blog_post_tag_lnks on p.post_id equals pt.post_id
                        join t in DB.blog_tag_tbls on pt.tag_id equals t.tag_id
                        where p.ispublished == true && p.isdeleted == false && p.create_dt >= start && p.create_dt < end
                        orderby p.create_dt descending
                        select new PostSummary()
                        {
                            Id = p.post_id,
                            Title = p.title,
                            Url = p.url,
                            Body = p.body,
                            Created = p.create_dt,
                            Updated = p.update_dt,
                            Tags = (from pt2 in DB.blog_post_tag_lnks join t2 in DB.blog_tag_tbls on pt2.tag_id equals t2.tag_id where pt2.post_id == p.post_id select new Tag() { Id = t2.tag_id, Name = t2.tag_nm, Url = t2.url }).ToList()
                        };
            return new PagedList<PostSummary>(posts, index, postsPerPage);
        }

        public override Post GetPostForUrl(string url)
        {
                var posts = from p in DB.blog_post_tbls
                            where p.ispublished == true && p.isdeleted == false && p.url == url
                            select new Post()
                            {
                                Id = p.post_id,
                                Title = p.title,
                                Url = p.url,
                                Body = p.body,
                                Created = p.create_dt,
                                Updated = p.update_dt,
                                Tags = (from pt2 in DB.blog_post_tag_lnks join t2 in DB.blog_tag_tbls on pt2.tag_id equals t2.tag_id where pt2.post_id == p.post_id select new Tag() { Id = t2.tag_id, Name = t2.tag_nm, Url = t2.url }).ToList()
                            };
                return posts.SingleOrDefault();
        }

        public override List<TagWeight> GetTagWeights()
        {
                var posts = from t in DB.blog_tag_tbls
                            join pt in DB.blog_post_tag_lnks on t.tag_id equals pt.tag_id
                            join p in DB.blog_post_tbls on pt.post_id equals p.post_id
                            where p.ispublished == true && p.isdeleted == false
                            group t by new { Id = t.tag_id, Name = t.tag_nm, Url = t.url } into t
                            orderby t.Key.Name
                            select new TagWeight()
                            {
                                Id = t.Key.Id,
                                Name = t.Key.Name,
                                Url = t.Key.Url,
                                Count = (from t2 in DB.blog_tag_tbls
                                    join pt2 in DB.blog_post_tag_lnks on t2.tag_id equals pt2.tag_id
                                    join p2 in DB.blog_post_tbls on pt2.post_id equals p2.post_id
                                         where p2.ispublished == true && p2.isdeleted == false && t.Key.Id == t2.tag_id
                                         select t2).Count()
                            };
                return posts.ToList();
        }

        public override PagedList<ArchiveCount> GetRecentArchiveCounts(int numberOfCounts)
        {
                var counts = from p in DB.blog_post_tbls
                            where p.ispublished == true && p.isdeleted == false
                            group p by new { Year = p.create_dt.Value.Year, Month = p.create_dt.Value.Month } into a
                            orderby a.Key.Year descending, a.Key.Month descending
                            select new ArchiveCount()
                            {
                                Year = a.Key.Year,
                                Month = a.Key.Month,
                                TotalPosts = a.Count()
                            };
                return new PagedList<ArchiveCount>(counts, 1, numberOfCounts);
        }

        public override List<ArchiveCount> GetArchiveCounts()
        {
            var counts = from p in DB.blog_post_tbls
                         where p.ispublished == true && p.isdeleted == false
                         group p by new { Year = p.create_dt.Value.Year, Month = p.create_dt.Value.Month } into a
                         orderby a.Key.Year descending, a.Key.Month descending
                         select new ArchiveCount()
                         {
                             Year = a.Key.Year,
                             Month = a.Key.Month,
                             TotalPosts = a.Count()
                         };
            return counts.ToList();
        }

        private BlogDatabaseDataContext DB
        {
            get 
            {
                return mDB;
            }
        }

        private string ConnectionString
        {
            get
            {
                LinqBusinessObjectConfigurationHandler config = Configuration.GetConfiguration<LinqBusinessObjectConfigurationHandler>("BlogDbSettings");
                return config.ConnectionString;
            }
        }

        void Dispose()
        {
            if (mDB != null)
            {
                mDB.Dispose();
            }
        }

        void IDisposable.Dispose()
        {
            Dispose();
        }
    }
}
